﻿CREATE PROCEDURE [dbo].[spCreateSequenceStepsForOrder]
	@SequenceID int, 
	@SequenceOrderID int,
	@PrevSequenceStepID int,
	@PrevSequenceOrderStepID int
AS
begin
	if @SequenceOrderID is null
		RAISERROR( 'spCreateSequenceStepsForOrder was called with a NULL SequenceOrderID', 18, -1 )

	declare cSequenceStepCursor cursor local for
	select SequenceStepID from tblSequenceStep
	where SequenceID = @SequenceID
		and coalesce( PrevSequenceStepID, -1 ) = coalesce(@PrevSequenceStepID, -1)

	declare @SequenceStepID int
	open cSequenceStepCursor
	fetch next from cSequenceStepCursor into @SequenceStepID
	while @@FETCH_STATUS = 0
	begin
		declare @SequenceOrderStepID int

		insert into tblSequenceOrderStep ( SequenceStepID, SequenceOrderID, Delay, RecurrenceTerm, RecurrenceCount, CurrentCount, NextTriggerDate, LastOrderID, Shipping, PrevSequenceOrderStepID )
		select @SequenceStepID, @SequenceOrderID, Delay, RecurrenceTerm, RecurrenceCount, null, null, null, Shipping, @PrevSequenceOrderStepID
		from tblSequenceStep
		where SequenceStepID = @SequenceStepID

		set @SequenceOrderStepID = SCOPE_IDENTITY()

		insert into tblSequenceOrderStepProduct ( SequenceOrderStepID, SequenceStepProductID, Qty, UnitPrice, Discount, ProductID )
		select @SequenceOrderStepID, SequenceStepProductID, Qty, UnitPrice, 0, ProductID
		from tblSequenceStepProduct
		where SequenceStepID = @SequenceStepID

		exec spCreateSequenceStepsForOrder @SequenceID, @SequenceOrderID, @SequenceStepID, @SequenceOrderStepID

		fetch next from cSequenceStepCursor into @SequenceStepID
	end
	close cSequenceStepCursor
	deallocate cSequenceStepCursor

	-- Set up the initial trigger date.
	update T1 set CurrentCount = 1, NextTriggerDate = DATEADD(day, T1.Delay, getdate())
	from tblSequenceOrderStep T1
		join tblSequenceStep T2 on T1.SequenceStepID = T2.SequenceStepID
	where T1.SequenceOrderID = @SequenceOrderID
		and T2.PrevSequenceStepID is null
end
